﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using Jayden.Dll.Core.Persistence;
using Jayden.Dll.Core.Persistence.Data;
using Jayden.Dll.Core.Persistence.Data.SQLite;
using Jayden.Dll.Core.Persistence.Enums;
using Jayden.Dll.Core.Persistence.Models;

namespace Jayden.Dll.Core.Membership
{
	public class SQLiteUserDataAccess : SQLiteDataAccess<User>, UserDataAccess
	{
		public SQLiteUserDataAccess(Configuration config, UnitOfWork uow, Entity entity) : base(config, uow, entity) { }

		protected override User CreateFromReader(DbDataReader reader)
		{
			User obj = null;
			if (reader.Read())
			{
				ObjectId oid = UnitOfWork.NewObjectId(Entity);
				AssignObjectIdFromReader(oid, reader);
				obj = UnitOfWork.GetRegistered<User>(oid);
				if (obj != null)
					return obj;
				// extract data for creating the membership user object
				string name = GetPropertyValueFromDataReader(Entity["UserName"], reader) as string;
				long memberId = Convert.ToInt64(GetPropertyValueFromDataReader(Entity["MemberId"], reader));
				string email = GetPropertyValueFromDataReader(Entity["Email"], reader) as string;
				string passwordQuestion = GetPropertyValueFromDataReader(Entity["PasswordQuestion"], reader) as string;
				string comment = GetPropertyValueFromDataReader(Entity["Comment"], reader) as string;
				bool isApproved = Convert.ToBoolean(GetPropertyValueFromDataReader(Entity["IsApproved"], reader));
				bool isLockedOut = Convert.ToBoolean(GetPropertyValueFromDataReader(Entity["IsLockedOut"], reader));
				DateTime creationDate = (DateTime)GetPropertyValueFromDataReader(Entity["CreationDate"], reader);
				DateTime lastLoginDate = (DateTime)GetPropertyValueFromDataReader(Entity["LastLoginDate"], reader);
				DateTime lastActivityDate = (DateTime)GetPropertyValueFromDataReader(Entity["LastActivityDate"], reader);
				DateTime lastPasswordChangedDate = (DateTime)GetPropertyValueFromDataReader(Entity["LastPasswordChangedDate"], reader);
				DateTime lastLockoutDate = (DateTime)GetPropertyValueFromDataReader(Entity["LastLockoutDate"], reader);
				obj = new User(UnitOfWork, "Jayden", name, memberId, email, passwordQuestion, comment, isApproved, isLockedOut, creationDate, lastLoginDate, lastActivityDate, lastPasswordChangedDate, lastLockoutDate);
				foreach (Property p in Entity.Properties)
					p.SetValue(obj, GetPropertyValueFromDataReader(p, reader));
				obj.State = ObjectState.Clean;
			}
			return obj;
		}

		public User ReadByName(string username)
		{
			string selectCommandText = Helper.GenerateSelect(Entity, Entity["UserName"]);
			using (DbCommandProxy selectCommand = CreateCommand(selectCommandText))
			{
				CreateParameter(selectCommand, "UserName", username);
				using (DbDataReader reader = selectCommand.Command.ExecuteReader())
				{
					return CreateFromReader(reader);
				}
			}
		}

		public User ReadByEmail(string email)
		{
			string selectCommandText = Helper.GenerateSelect(Entity, Entity["Email"]);
			using (DbCommandProxy selectCommand = CreateCommand(selectCommandText))
			{
				CreateParameter(selectCommand, "Email", email);
				using (DbDataReader reader = selectCommand.Command.ExecuteReader())
				{
					return CreateFromReader(reader);
				}
			}
		}

		public List<User> FindAll(int page, int pageSize, out int totalRecords)
		{
			string countCommandText = Helper.GenerateCount(Entity.Table.TableName, null, null);
			using (DbCommandProxy countCommand = CreateCommand(countCommandText))
			{
				object countObject = countCommand.Command.ExecuteScalar();
				string countString = countObject == null ? "0" : countObject.ToString();
				totalRecords = int.Parse(countString);
			}
			string selectCommandText = Helper.GenerateSelect(null, Entity.Table.TableName, null, null, null, pageSize, page * pageSize);
			using (DbCommandProxy selectCommand = CreateCommand(selectCommandText))
			{
				using (DbDataReader reader = selectCommand.Command.ExecuteReader())
				{
					List<User> users = new List<User>(EnumerateFromReader(reader));
					return users;
				}
			}
		}

		public List<User> FindByName(string nameLike, int page, int pageSize, out int totalRecords)
		{
			string countCommandText = Helper.GenerateCount(Entity.Table.TableName, "UserName LIKE @UserName", null);
			using (DbCommandProxy countCommand = CreateCommand(countCommandText))
			{
				CreateParameter(countCommand, "UserName", nameLike);
				object countObject = countCommand.Command.ExecuteScalar();
				string countString = countObject == null ? "0" : countObject.ToString();
				totalRecords = int.Parse(countString);
			}
			string selectCommandText = Helper.GenerateSelect(null, Entity.Table.TableName, "UserName LIKE @UserName", null, null, pageSize, page * pageSize);
			using (DbCommandProxy selectCommand = CreateCommand(selectCommandText))
			{
				CreateParameter(selectCommand, "UserName", nameLike);
				using (DbDataReader reader = selectCommand.Command.ExecuteReader())
				{
					List<User> users = new List<User>(EnumerateFromReader(reader));
					return users;
				}
			}
		}

		public List<User> FindByEmail(string emailLike, int page, int pageSize, out int totalRecords)
		{
			string countCommandText = Helper.GenerateCount(Entity.Table.TableName, "Email LIKE @Email", null);
			using (DbCommandProxy countCommand = CreateCommand(countCommandText))
			{
				CreateParameter(countCommand, "Email", emailLike);
				object countObject = countCommand.Command.ExecuteScalar();
				string countString = countObject == null ? "0" : countObject.ToString();
				totalRecords = int.Parse(countString);
			}
			string selectCommandText = Helper.GenerateSelect(null, Entity.Table.TableName, "Email LIKE @Email", null, null, pageSize, page * pageSize);
			using (DbCommandProxy selectCommand = CreateCommand(selectCommandText))
			{
				CreateParameter(selectCommand, "Email", emailLike);
				using (DbDataReader reader = selectCommand.Command.ExecuteReader())
				{
					List<User> users = new List<User>(EnumerateFromReader(reader));
					return users;
				}
			}
		}


		public List<User> Search(string term, bool name, bool email, bool firstName, bool lastName, int page, int pageSize, out int totalRecords)
		{
			List<string> clauses = new List<string>();
			if (name)
				clauses.Add("UserName LIKE @Term");
			if (email)
				clauses.Add("Email LIKE @Term");
			if (firstName)
				clauses.Add("FirstName LIKE @Term");
			if (lastName)
				clauses.Add("LastName LIKE @Term");
			string clauseString = string.Join(" OR ", clauses);
			string countCommandText = Helper.GenerateCount(Entity.Table.TableName, clauseString, null);
			using (DbCommandProxy countCommand = CreateCommand(countCommandText))
			{
				CreateParameter(countCommand, "Term", term);
				object countObject = countCommand.Command.ExecuteScalar();
				string countString = countObject == null ? "0" : countObject.ToString();
				totalRecords = int.Parse(countString);
			}
			string selectCommandText = Helper.GenerateSelect(null, Entity.Table.TableName, clauseString, null, null, pageSize, page * pageSize);
			using (DbCommandProxy selectCommand = CreateCommand(selectCommandText))
			{
				CreateParameter(selectCommand, "Term", term);
				using (DbDataReader reader = selectCommand.Command.ExecuteReader())
				{
					List<User> users = new List<User>(EnumerateFromReader(reader));
					return users;
				}
			}
		}
	}
}
